package wangbaguanlixitong.dao;


import wangbaguanlixitong.model.Hy;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

public class HyDao {
   //添加会员
   public void add(Hy hy) {
       Connection conn = null;
       PreparedStatement pstmt = null;

       String sql = "insert into t_hy(card,name,idcard,sex,age,tel,yuan) values(?,?,?,?,?,?,?)";
       try {
           conn = Dbutil.getConnection();
           pstmt = conn.prepareStatement(sql);

           pstmt.setString(1,hy.getCard());
           pstmt.setString(2,hy.getName());
           pstmt.setString(3,hy.getIdcard());
           pstmt.setString(4,hy.getSex());
           pstmt.setString(5,hy.getAge());
           pstmt.setString(6,hy.getTel());
           pstmt.setString(7,hy.getYuan());
           pstmt.executeUpdate();

       } catch (ClassNotFoundException | SQLException e) {
           e.printStackTrace();
       } finally {
        Dbutil.release(pstmt,conn);
    }
   }

   //查看会员
   public List<Hy> selecthuiyuan() {

       Connection conn = null;
       ResultSet rs = null;
       PreparedStatement pstmt = null;
       List<Hy> hyList = new ArrayList<>();
       try {
//           String sql = "select * from t_hy where card=? and name=? and idcard=? and sex=? and age=? and tel=? and yuan=?";
           String sql = "select * from t_hy";

           conn = Dbutil.getConnection();
           pstmt = conn.prepareStatement(sql);
        /*   pstmt.setString(1, card);
           pstmt.setString(2, name);
           pstmt.setString(3, idcard);
           pstmt.setString(4, sex);
           pstmt.setString(5, age);
           pstmt.setString(6, tel);
           pstmt.setString(7, yuan);*/


           rs = pstmt.executeQuery();

           while (rs.next()){
               Hy hy = new Hy();
               hy.setCard(rs.getString(1));
               hy.setName(rs.getString(2));
               hy.setIdcard(rs.getString(3));
               hy.setSex(rs.getString(4));
               hy.setAge(rs.getString(5));
               hy.setTel(rs.getString(6));
               hy.setYuan(rs.getString(7));

               hyList.add(hy);
           }
       } catch (Exception ex) {
           ex.printStackTrace();

       }
       finally {
           Dbutil.release(pstmt,conn);
       }
       return hyList;

   }

   //删除会员
   public  void detele(String name) {
       Connection conn = null;
       PreparedStatement pstmt = null;
       String sql = "delete from t_hy where name = ?";
       try {
           conn = Dbutil.getConnection();
           pstmt = conn.prepareStatement(sql);
           pstmt.setString(1, name);
           pstmt.executeUpdate();

       } catch (SQLException | ClassNotFoundException throwables) {
           throwables.printStackTrace();
       } finally {
           Dbutil.release(pstmt, conn);
       }
   }

   //修改会员
    public  void  uphuiyuan(Hy  hy){
        
        Connection connection=null;
        PreparedStatement preparedStatement = null;

        try {
           //String sql = "update t_hy set card = ?,name = ?,idcard = ?,sex = ?,age= ?,tel = ?,yuan= ? where name = ? ";
            String sql = "update t_hy set card = ?,idcard = ?,sex = ?,age= ?,tel = ?,yuan= ? where name = ? ";
            connection = Dbutil.getConnection();
            preparedStatement= connection.prepareStatement(sql);

            //Hy hy = new Hy();
            preparedStatement.setString(1,hy.getCard());
            //preparedStatement.setString(2,hy.getName());
            preparedStatement.setString(2,hy.getIdcard());
            preparedStatement.setString(3,hy.getSex());
            preparedStatement.setString(4,hy.getAge());
            preparedStatement.setString(5,hy.getTel());
            preparedStatement.setString(6,hy.getYuan());
            preparedStatement.setString(7,hy.getName());

            preparedStatement.executeUpdate();



        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutil.release(preparedStatement,connection);
        }



    }

    //获取会员名字
    public Hy getHyByName(String name) throws ClassNotFoundException,SQLException{
       Hy hy =null;
       Connection conn = Dbutil.getConnection();
       String sql ="select  * from t_hy where name = ?";
       PreparedStatement ps = conn.prepareStatement(sql);
       ps.setString(1,name);
       ResultSet rs = ps.executeQuery();
       while (rs.next()){
           hy = new Hy();
           hy.setCard(rs.getString("card"));
           hy.setName(rs.getString("name"));
           hy.setIdcard(rs.getString("idcard"));
           hy.setSex(rs.getString("sex"));
           hy.setAge(rs.getString("age"));
           hy.setTel(rs.getString("tel"));
           hy.setYuan(rs.getString("yuan"));
       }
       return hy;
    }


//修改会员
//public Hy uphuiyuan(String card, String name, String idcard,
//                      String sex, String age, String tel, String yuan)
//{
//    Hy hy = new Hy();
//    Connection conn = null;
//    PreparedStatement pstmt = null;
//    String sql = "update t_hy set card = ?,name = ?,idcard = ?,sex = ?,age= ?,tel = ?,yuan= ? where name = ?";
//    try {
//
//        conn = Dbutil.getConnection();
//        pstmt = conn.prepareStatement(sql);
//        pstmt.setString(1,hy.getCard());
//        pstmt.setString(2,hy.getName());
//        pstmt.setString(3,hy.getIdcard());
//        pstmt.setString(4,hy.getSex());
//        pstmt.setString(5,hy.getAge());
//        pstmt.setString(6,hy.getTel());
//        pstmt.setString(7, hy.getYuan());
//
//        pstmt.executeUpdate();
//    } catch (SQLException | ClassNotFoundException throwables) {
//        throwables.printStackTrace();
//    } finally {
//        Dbutil.release(pstmt,conn);
//    }
//    return hy;
//}


}




